package com.dudu.dao;

import java.sql.Connection;
import java.util.List;

import net.sf.json.JSONObject;

import com.dudu.model.RoleUser;

import core.CoreDao;
import core.CoreDaoInvoke;
import core.db.DBConnectionUtil;

public class RoleUserDao {

	public static void main(String[] args) {

		CoreDao coreDao = new CoreDaoInvoke();
		RoleUserDao dao = new RoleUserDao();

		RoleUser bean = new RoleUser();
		bean.setRole_id(1);
		bean.setUser_id(1);
		
		dao.insert(coreDao, bean);
		//dao.updateLastLoginTime(coreDao, bean);
		dao.queryListByUserId(coreDao, 1);
	}

	public int insert(CoreDao dao, RoleUser bean) {

		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					"INSERT INTO t_role_user(role_id, user_id)");
			sql.append("VALUES(?, ?)");

			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] { 
						bean.getRole_id(), bean.getUser_id()
					});

			JSONObject obj = list.get(0);
			result = obj.optInt("result");

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	public List<RoleUser> queryListByUserId(CoreDao dao, int userId) {

		Connection con = null;
		List<RoleUser> result = null;

		try {
			con = DBConnectionUtil.getDBConnection("emate1");

			StringBuffer sql = new StringBuffer(
					"select id, role_id, user_id FROM t_role_user where user_id=?");
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { userId });
			if (list != null && list.size() > 0) {
				result = dao.covertToBean(list, RoleUser.class);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	public RoleUser queryByUserId(CoreDao dao, int userId) {

		Connection con = null;
		RoleUser result = null;

		try {
			con = DBConnectionUtil.getDBConnection("emate1");

			StringBuffer sql = new StringBuffer(
					"select id, role_id, user_id FROM t_role_user where user_id=?");
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { userId });
			if (list != null && list.size() > 0) {
				List<RoleUser> itemList= dao.covertToBean(list, RoleUser.class);
				if(itemList.size() > 0){
					result = itemList.get(0);
				}
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	public List<JSONObject> queryUserByRole(CoreDao dao, int roleid) {

		Connection con = null;
		List<JSONObject> result = null;
		
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					" SELECT u.*,IFNULL((SELECT r.id FROM t_role_user r WHERE r.user_id=u.id AND r.role_id=?), -1)AS check_status " +
					" FROM t_user u order by u.create_time desc ");
			result = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { roleid });

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	public int deleteByRoleid(CoreDao dao, int roleid) {

		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			
			StringBuffer sql = new StringBuffer(" delete from t_role_user where role_id =? ");
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { roleid });

			if (list != null && list.size() > 0) {
				list.get(0);
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
}
